# 安装依赖库
#pip install pandas openpyxl jinja2 python-docx

import pandas as pd
from datetime import datetime

# 定义风险分类规则（可扩展）
RISK_RULES = {
    "登录风险": ["登录失败", "异常IP", "双因素认证"],
    "数据泄露风险": ["导出", "下载", "客户名单", "联系方式", "录音"],
    "权限风险": ["权限配置", "添加权限", "停用账号", "API接口管理"],
    "操作合规风险": ["删除", "合并", "未审批", "非工作时间"],
    "审批冲突风险": ["合同审批", "金额"]
}

def classify_risk(operation_detail):
    """根据操作详情匹配风险类型"""
    risks = []
    for category, keywords in RISK_RULES.items():
        if any(keyword in operation_detail for keyword in keywords):
            risks.append(category)
    return ", ".join(risks) if risks else "低风险"

# 读取Sheet4数据
df = pd.read_excel("/Users/jijiuxue/Downloads/数据分析.xlsx", sheet_name="Sheet4")

# 数据预处理
df["操作时间"] = pd.to_datetime(df["时间戳"])
df["操作时段"] = df["操作时间"].apply(
    lambda x: "工作时间" if 9 <= x.hour < 18 else "非工作时间"
)

# 风险分类
df["风险类型"] = df["操作详情"].apply(classify_risk)

# 保存分类结果
df.to_excel("分类后操作记录.xlsx", index=False)


from jinja2 import Environment, FileSystemLoader
from docx import Document
import matplotlib.pyplot as plt
# 设置中文字体
plt.rcParams['font.sans-serif'] = ['Songti SC']  # 根据系统可用字体调整
plt.rcParams['axes.unicode_minus'] = False  # 解决负号乱码
def generate_report(df):
    """生成Word格式风险报告"""
    
    # 统计分析
    risk_stats = df["风险类型"].value_counts().to_dict()
    top_risky_users = df[df["风险类型"] != "低风险"]["用户名称"].value_counts().head(3).to_dict()
    
    # 生成风险趋势图
    df["日期"] = df["操作时间"].dt.date
    daily_risks = df.groupby(["日期", "风险类型"]).size().unstack(fill_value=0)
    daily_risks.plot(kind="line", figsize=(10,6))
    plt.title("每日风险操作趋势")
    plt.savefig("risk_trend.png")
    plt.close()

    # 使用Jinja2模板引擎
    env = Environment(loader=FileSystemLoader('.'))
    template = env.get_template("report_template.docx")

    # 渲染数据到模板
    context = {
        "report_date": datetime.now().strftime("%Y-%m-%d"),
        "total_operations": len(df),
        "high_risk_ratio": f"{len(df[df['风险类型'] != '低风险'])/len(df)*100:.1f}%",
        "risk_stats": risk_stats,
        "top_risky_users": top_risky_users,
        "trend_chart": "risk_trend.png"
    }
    
    # 生成Word文档
    doc = Document()
    doc.add_heading("系统操作风险分析报告", level=1)
    doc.add_paragraph(f"生成时间：{context['report_date']}")
    
    # 添加统计表格
    table = doc.add_table(rows=1, cols=3)
    table.style = "LightShading"
    hdr_cells = table.rows[0].cells
    hdr_cells[0].text = "风险类型"
    hdr_cells[1].text = "数量"
    hdr_cells[2].text = "占比"
    
    for risk, count in context["risk_stats"].items():
        row_cells = table.add_row().cells
        row_cells[0].text = risk
        row_cells[1].text = str(count)
        row_cells[2].text = f"{(count/context['total_operations'])*100:.1f}%"
    
    # 保存报告
    doc.save("系统操作风险分析报告.docx")

# 执行报告生成
generate_report(pd.read_excel("分类后操作记录.xlsx"))


